import logging
from datetime import datetime
from typing import Optional, List

from fastapi import APIRouter, Query, Path
from fastapi.params import Depends
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session, sessionmaker

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

scene_head_router = APIRouter(prefix="/sceneHead", tags=["触发场景管理"])


class SceneSearch(PaginationBase):
    fsiteNo: str = Field(None, description="站点编号")
    fscenetype: str = Field(None, description="")


# todo 14.触发场景设置
@scene_head_router.get("/list", summary="查询触发场景")
def list_scene_head(scene: SceneSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from y_scene_head a,s_ts_equipment_info b"
    sql = sql + " where 1=1"
    sql = sql + " and a.feuipmentid=b.feuipmentid"
    if scene.fsiteNo is not None:
        sql = sql + " and fsite_no= :fsiteNo "
        param["fsiteNo"] = scene.fsiteNo
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select a.enable,b.fequipmentname fequipmenttag,a.feuipmentid,a.foperator"
    sql = sql + ",a.frange,a.fscenename,a.fsceneno,a.fsite_no,a.fupddate"
    sql = sql + " from y_scene_head a,s_ts_equipment_info b"
    sql = sql + " where 1=1"
    sql = sql + " and a.feuipmentid=b.feuipmentid"
    if scene.fsiteNo is not None:
        sql = sql + " and fsite_no= :fsiteNo "
        param["fsiteNo"] = scene.fsiteNo
    sql = sql + " limit :limit offset :offset "
    param["limit"] = scene.limit
    param["offset"] = scene.offset
    data = db.execute(text(sql), param).fetchall()

    # atsPoints 明细列表
    param = {}
    sql_3 = " select b.fentryid,b.fsceneno,b.fpoint_no fpointno "
    sql_3 = sql_3 + " ,c.fpoint_name fpointName"
    sql_3 = sql_3 + " from y_scene_head a"
    sql_3 = sql_3 + " inner join y_scene_point b on a.fsceneno=b.fsceneno"
    sql_3 = sql_3 + " left join a_ts_point c on b.fpoint_no=c.fpoint_no"
    sql_3 = sql_3 + " where 1=1 "
    if scene.fsiteNo is not None:
        sql_3 = sql_3 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = scene.fsiteNo
    sql_3 = sql_3 + " order by b.fsceneno,b.fentryid"
    # print(sql_3)
    data_detail = db.execute(text(sql_3), param).fetchall()

    # sTsEquipmentList 明细列表
    param = {}
    sql_2 = " select b.faction,b.factiontype,b.fentryid,b.fequipmentbarcode,b.fequipmenttype"
    sql_2 = sql_2 + ",c.fdictname,b.finstructions,b.fposition,b.fsceneno"
    sql_2 = sql_2 + " from y_scene_head a,y_scene_detail b,s_ts_equipment_dict c "
    sql_2 = sql_2 + " where a.fsceneno=b.fsceneno "
    sql_2 = sql_2 + " and b.fequipmenttype=c.fdictno"
    if scene.fsiteNo is not None:
        sql_2 = sql_2 + " and a.fsite_no= :fsiteNo"
        param["fsiteNo"] = scene.fsiteNo
    sql_2 = sql_2 + " order by b.fsceneno,b.factiontype,b.fentryid"
    # print(sql_2)
    data_detail2 = db.execute(text(sql_2), param).fetchall()

    # sTcSceneItemList 明细列表
    param = {}
    sql_4 = " select b.fno,b.fparafield,b.fparafieldname,b.fsceneno"
    sql_4 = sql_4 + ",b.fvalue,b.fvalue2,b.fwhere,b.fwhere2"
    sql_4 = sql_4 + " from y_scene_head a,y_scene_item b"
    sql_4 = sql_4 + " where a.fsceneno=b.fsceneno "
    if scene.fsiteNo is not None:
        sql_4 = sql_4 + " and a.fsite_no= :fsiteNo "
        param["fsiteNo"] = scene.fsiteNo
    sql_4 = sql_4 + " order by b.fsceneno,b.fno"
    # print(sql_4)
    data_detail4 = db.execute(text(sql_4), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['enable'] = data[key][0]
        d1['fequipmenttag'] = data[key][1]
        d1['feuipmentid'] = data[key][2]
        d1['foperator'] = data[key][3]
        d1['fparafield'] = None
        d1['fpointNo'] = None
        d1['frange'] = data[key][4]
        d1['fscenename'] = data[key][5]
        d1['fsceneno'] = data[key][6]
        d1['fsiteNo'] = data[key][7]
        d1['fupddate'] = data[key][8]
        d1['fvalue'] = None
        d1['fvalue2'] = None
        d1['fwhere'] = None
        d1['fwhere2'] = None
        sbillno = data[key][6]

        atsPoints = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][1]) == str(sbillno)):
                d2 = {}
                d2['fentryid'] = data_detail[j1][0]
                d2['fsceneno'] = data_detail[j1][1]
                d2['fpointNo'] = data_detail[j1][2]
                d2['fpointName'] = data_detail[j1][3]
                atsPoints.append(d2)

        sTcSceneItemList = []
        for j3 in range(len(data_detail4)):
            if (str(data_detail4[j3][3]) == str(sbillno)):
                d4 = {}
                d4['fno'] = data_detail4[j3][0]
                d4['fparafield'] = data_detail4[j3][1]
                d4['fparafieldname'] = data_detail4[j3][2]
                d4['fsceneno'] = data_detail4[j3][3]
                d4['fvalue'] = data_detail4[j3][4]
                d4['fvalue2'] = data_detail4[j3][5]
                d4['fwhere'] = data_detail4[j3][6]
                d4['fwhere2'] = data_detail4[j3][7]
                sTcSceneItemList.append(d4)

        sTsEquipmentList = []
        sTsEquipmentList2 = []
        for j2 in range(len(data_detail2)):
            if (str(data_detail2[j2][8]) == str(sbillno)):
                d3 = {}
                d3['faction'] = data_detail2[j2][0]
                d3['factiontype'] = data_detail2[j2][1]
                d3['fentryid'] = data_detail2[j2][2]
                d3['fequipmentbarcode'] = data_detail2[j2][3]
                d3['fequipmenttag'] = None
                d3['fequipmenttype'] = data_detail2[j2][4]
                d3['feuipmentname'] = data_detail2[j2][5]
                d3['finstructions'] = data_detail2[j2][6]
                d3['fposition'] = data_detail2[j2][7]
                d3['fsceneno'] = data_detail2[j2][8]
                if data_detail2[j2][1] == 1:  # 1:超标，2:达标
                    sTsEquipmentList.append(d3)
                else:
                    sTsEquipmentList2.append(d3)

        d1["atsPoints"] = atsPoints
        d1["sTsEquipmentList"] = sTsEquipmentList  # 超标设备
        d1["sTcSceneItemList"] = sTcSceneItemList
        d1["sTsEquipmentList2"] = sTsEquipmentList2  # 达标设备
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": scene.limit,
                 "page": scene.pageIndex,
                 "pages": (count + scene.limit - 1) // scene.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class TriggerSceneCreate(BaseModel):
    """触发场景主表创建模型"""
    fscenename: str = Field(..., description="场景名称")
    fsiteNo: str = Field(..., description="站点编号")
    feuipmentid: int = Field(..., description="设备ID")
    frange: int = Field(..., description="执行范围（分钟）")
    enable: int = Field(..., description="启用状态（1:启用，0:停用）")
    fscenetype: int = Field(..., description="场景类型（如:1-触发式）")

    # 自动填充字段
    fsceneno: Optional[int] = Field(None, description="场景单号（自动生成）")
    foperator: Optional[str] = Field(None, description="操作人ID")
    fupddate: Optional[datetime] = Field(None, description="更新时间")


class SceneDeviceDetail(BaseModel):
    """场景设备明细模型（通用，区分超标/达标类型）"""
    fequipmenttype: int = Field(..., description="设备类型ID")
    faction: str = Field(..., description="操作名称")
    finstructions: str = Field(..., description="操作指令")
    factiontype: int = Field(..., description="操作类型（1:超标处理，2:达标处理）")


class SceneConditionItem(BaseModel):
    """场景条件项模型"""
    fparafield: str = Field(..., description="参数字段名")
    fwhere: str = Field(..., description="条件表达式")
    fvalue: float = Field(..., description="参数值")
    fwhere2: Optional[str] = Field(None, description="二级条件表达式")
    fvalue2: Optional[float] = Field(None, description="二级参数值")


class FullTriggerSceneCreate(TriggerSceneCreate):
    """触发场景完整创建模型（包含所有子表数据）"""
    sTsEquipmentList: List[SceneDeviceDetail] = Field(..., description="超标设备列表")
    sTsEquipmentList2: List[SceneDeviceDetail] = Field(..., description="达标设备列表")
    sTcSceneItemList: List[SceneConditionItem] = Field(..., description="条件列表")
    atsPoints: List[str] = Field(..., description="教室点位列表")


@scene_head_router.post("/create", summary="新增触发场景")
def create_scene_head(scene: FullTriggerSceneCreate, db: Session = Depends(get_db),
                      token: str = Depends(oauth2_scheme)):
    try:
        foperator = 3
        if token is not None:
            foperator = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            # 先取单号
            fsceneno = 0
            sql = "select nextval('y_scene_head_fsceneno_seq') as last_value "
            res = db.execute(text(sql)).fetchall()
            fsceneno = res[0][0]
            # 返回内容
            d1 = {}
            sql = "insert into y_scene_head(fsceneno,fscenename,fsite_no,feuipmentid,frange,enable,foperator,fupddate)"
            sql = sql + " values( :fsceneno , :fscenename , :fsiteNo , :feuipmentid , :frange , :enable , :foperator ,now());"
            db.execute(text(sql), {
                "fsceneno": fsceneno,
                "fscenename": scene.fscenename,
                "fsiteNo": scene.fsiteNo,
                "feuipmentid": scene.feuipmentid,
                "frange": scene.frange,
                "enable": scene.enable,
                "foperator": foperator,
            })
            # 超标设备列表
            iList = 1
            sTsEquipmentList = scene.sTsEquipmentList
            for sts in sTsEquipmentList:
                sql = "insert into y_scene_detail(fsceneno,fequipmenttype,faction,finstructions,factiontype)"
                sql = sql + " values( :fsceneno , :fequipmenttype , :faction , :finstructions ,1 );"
                db.execute(text(sql), {
                    "fsceneno": fsceneno,
                    "fequipmenttype": sts.fequipmenttype,
                    "faction": sts.faction,
                    "finstructions": sts.finstructions,
                })
                iList = iList + 1
            # 达标设备列表
            sTsEquipmentList2 = scene.sTsEquipmentList2
            for sts in sTsEquipmentList2:
                sql = "insert into y_scene_detail(fsceneno,fequipmenttype,faction,finstructions,factiontype)"
                sql = sql + " values( :fsceneno , :fequipmenttype , :faction , :finstructions ,2 );"
                db.execute(text(sql), {
                    "fsceneno": fsceneno,
                    "fequipmenttype": sts.fequipmenttype,
                    "faction": sts.faction,
                    "finstructions": sts.finstructions,
                })
                iList = iList + 1
            # 条件列表
            sTcSceneItemList = scene.sTcSceneItemList
            for sts in sTcSceneItemList:
                sql = "insert into y_scene_item(fsceneno,fparafield,fwhere,fvalue,fwhere2,fvalue2)"
                sql = sql + " values( :fsceneno , :fparafield , :fwhere , :fvalue , :fwhere2 , :fvalue2 );"
                db.execute(text(sql), {
                    "fsceneno": fsceneno,
                    "fparafield": sts.fparafield,
                    "fwhere": sts.fwhere,
                    "fvalue": sts.fvalue,
                    "fwhere2": sts.fwhere2,
                    "fvalue2": sts.fvalue2,
                })
            # 教室列表
            points = scene.atsPoints
            for point in points:
                sql = "insert into y_scene_point(fsceneno,fpoint_no)"
                sql = sql + " values( :fsceneno , :fpointNo  );"
                db.execute(text(sql), {
                    "fsceneno": fsceneno,
                    "fpointNo": point
                })
            # 返回
            d1["enable"] = scene.enable
            # d1["fequipmenttag"] = scene.fequipmenttag
            d1["feuipmentid"] = scene.feuipmentid
            d1["foperator"] = scene.foperator
            d1["fparafield"] = None
            d1["fpointNo"] = None
            d1["frange"] = scene.frange
            d1["fscenename"] = scene.fscenename
            d1["fsceneno"] = fsceneno
            d1["fsiteNo"] = scene.fsiteNo
            d1["fupddate"] = None
            d1["fpointName"] = None
            d1["fpointNo"] = None
            d1["frange"] = scene.frange
            d1["fscenename"] = scene.fscenename
            d1["fsceneno"] = fsceneno
            d1["fscenetype"] = scene.fscenetype
            d1["fsiteNo"] = scene.fsiteNo
            d1["fupddate"] = None
            d1["fvalue"] = None
            d1["fvalue2"] = None
            d1["fwhere"] = None
            d1["fwhere2"] = None
            d1["atsPoints"] = scene.atsPoints
            d1["sTsEquipmentList"] = sTsEquipmentList
            d1["sTcSceneItemList"] = sTcSceneItemList
            d1["sTsEquipmentList2"] = sTsEquipmentList2
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@scene_head_router.post("/update", summary="更新触发场景")
def update_scene_head(scene: FullTriggerSceneCreate, db: Session = Depends(get_db),
                      token: str = Depends(oauth2_scheme)):
    try:
        user_id = EncryptionMiddleware.get_current_user(token)
        d1 = {}
        with db.begin():
            sql = "update y_scene_head set fscenename= :fscenename ,feuipmentid= :feuipmentid "
            sql = sql + " ,frange= :frange ,enable= :enable "
            sql = sql + " ,foperator= :foperator ,fupddate=now()"
            sql = sql + " where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "frange": scene.frange,
                "enable": scene.enable,
                "foperator": user_id,
                "fsceneno": scene.fsceneno,
            })

            # 超标设备列表
            sql = "delete from y_scene_detail where fsceneno= :fsceneno"
            db.execute(text(sql), {
                "fsceneno": scene.fsceneno
            })

            # 超标设备列表
            iList = 1
            sTsEquipmentList = scene.sTsEquipmentList
            for sts in sTsEquipmentList:
                sql = "insert into y_scene_detail(fsceneno,fequipmenttype,faction,finstructions,factiontype)"
                sql = sql + " values( :fsceneno , :fequipmenttype , :faction , :finstructions ,1 );"
                db.execute(text(sql), {
                    "fsceneno": scene.fsceneno,
                    "fequipmenttype": sts.fequipmenttype,
                    "faction": sts.faction,
                    "finstructions": sts.finstructions,
                })
                iList = iList + 1
            # 达标设备列表
            sTsEquipmentList2 = scene.sTsEquipmentList2
            for sts in sTsEquipmentList2:
                sql = "insert into y_scene_detail(fsceneno,fequipmenttype,faction,finstructions,factiontype)"
                sql = sql + " values( :fsceneno , :fequipmenttype , :faction , :finstructions ,2 );"
                db.execute(text(sql), {
                    "fsceneno": scene.fsceneno,
                    "fequipmenttype": sts.fequipmenttype,
                    "faction": sts.faction,
                    "finstructions": sts.finstructions,
                })
                iList = iList + 1
            # 条件列表
            sql = "delete from y_scene_item where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": scene.fsceneno,
            })
            sTcSceneItemList = scene.sTcSceneItemList
            for sts in sTcSceneItemList:
                sql = "insert into y_scene_item(fsceneno,fparafield,fwhere,fvalue,fwhere2,fvalue2)"
                sql = sql + " values( :fsceneno , :fparafield , :fwhere , :fvalue , :fwhere2 , :fvalue2 );"
                db.execute(text(sql), {
                    "fsceneno": scene.fsceneno,
                    "fparafield": sts.fparafield,
                    "fwhere": sts.fwhere,
                    "fvalue": sts.fvalue,
                    "fwhere2": sts.fwhere2,
                    "fvalue2": sts.fvalue2,
                })
            # 教室列表
            sql = "delete from y_scene_point where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": scene.fsceneno,
            })
            points = scene.atsPoints
            for point in points:
                sql = "insert into y_scene_point(fsceneno,fpoint_no)"
                sql = sql + " values( :fsceneno , :fpointNo  );"
                db.execute(text(sql), {
                    "fsceneno": scene.fsceneno,
                    "fpointNo": point
                })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@scene_head_router.post("/delete/{fsceneno}", summary="删除触发场景")
def delete_scene_head(fsceneno: int = Path(description="场景编号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "delete from y_scene_head where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno
            })
            sql = "delete from y_scene_detail where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno
            })
            sql = "delete from y_scene_item where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno
            })
            sql = "delete from y_scene_point where fsceneno= :fsceneno "
            db.execute(text(sql), {
                "fsceneno": fsceneno
            })
            return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
